package com.example.wechatdaka.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.wechatdaka.entity.Signin;
import com.example.wechatdaka.entity.SigninTotal;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author lx
 * @since 2020-11-30
 */
public interface SigninMapper extends BaseMapper<Signin> {
    @Select("select u.lxUsername as lxUserNumber,ck.lxCheckWorkName as lxCheckworkId,s.lxSignInTime,s.lxSignInStatus\n" +
            "from lx_signin s\n" +
            "left join lx_checkwork ck on s.lxCheckworkId=ck.id\n" +
            "left join lx_user u on s.lxUserNumber=u.lxUserNumber\n" +
            "where s.lxUserNumber=#{lxUserNumber} order by ck.lxReleaseTime desc")
    public List<Signin> querySigninMini(String lxUserNumber);
    @Select("select sin.id,ck.lxCheckWorkName as lxCheckworkId,c.lxClazzName,u.lxUserNumber,u.lxUsername,sin.lxSignInStatus,sin.lxSignInTime\n" +
            "from lx_signin sin\n" +
            "left join lx_checkwork ck on sin.lxCheckworkId=ck.id\n" +
            "left join lx_user u on u.lxUserNumber=sin.lxUserNumber\n" +
            "left join lx_user_student s on u.id=s.lxUserId\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "where sin.lxCheckworkId=#{lxCheckworkId}\n" +
            "ORDER BY sin.lxSignInStatus desc,c.id,sin.lxSignInTime desc")
    public IPage<Signin> querySignByCheckPage(Page page,String lxCheckworkId);
    @Select("select sin.id,ck.lxCheckWorkName as lxCheckworkId,c.lxClazzName,u.lxUserNumber,u.lxUsername,sin.lxSignInStatus,sin.lxSignInTime\n" +
            "from lx_signin sin\n" +
            "left join lx_checkwork ck on sin.lxCheckworkId=ck.id\n" +
            "left join lx_user u on u.lxUserNumber=sin.lxUserNumber\n" +
            "left join lx_user_student s on u.id=s.lxUserId\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "where sin.lxCheckworkId=#{lxCheckworkId} and u.lxUsername LIKE concat('%',#{keyword},'%')\n" +
            "ORDER BY sin.lxSignInStatus desc,c.id,sin.lxSignInTime desc")
    public IPage<Signin> querySignByCheckAndKeyword(Page page,String lxCheckworkId,String keyword);

    @Select("select sin.id,ck.lxCheckWorkName as lxCheckworkId,c.lxClazzName,u.lxUserNumber,u.lxUsername,sin.lxSignInStatus,sin.lxSignInTime\n" +
            "from lx_signin sin\n" +
            "left join lx_checkwork ck on sin.lxCheckworkId=ck.id\n" +
            "left join lx_user u on u.lxUserNumber=sin.lxUserNumber\n" +
            "left join lx_user_student s on u.id=s.lxUserId\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "ORDER BY ck.lxReleaseTime desc,sin.lxSignInStatus desc,c.id,sin.lxSignInTime desc")
    public IPage<Signin> queryAllSignPage(Page page);
    @Select("select sin.id,ck.lxCheckWorkName as lxCheckworkId,c.lxClazzName,u.lxUserNumber,u.lxUsername,sin.lxSignInStatus,sin.lxSignInTime\n" +
            "from lx_signin sin\n" +
            "left join lx_checkwork ck on sin.lxCheckworkId=ck.id\n" +
            "left join lx_user u on u.lxUserNumber=sin.lxUserNumber\n" +
            "left join lx_user_student s on u.id=s.lxUserId\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "where ck.lxUserId=#{lxUserId}\n" +
            "ORDER BY ck.lxReleaseTime desc,sin.lxSignInStatus desc,c.id,sin.lxSignInTime desc")
    public IPage<Signin> querySignByUserIdPage(Page page,String lxUserId);
    @Select("select ck.id,ck.lxCheckWorkName,u.lxUsername,ck.lxClazzName,\n" +
            "count(1) as totalNum,\n" +
            "sum(if(sin.lxSignInStatus=1,1,0)) as signinNum,\n" +
            "sum(if(sin.lxSignInStatus=2,1,0)) as lateNum ,\n" +
            "sum(if(sin.lxSignInStatus=3,1,0)) as leaveNum,\n" +
            "sum(if(sin.lxSignInStatus=4,1,0)) as absenceNum\n" +
            "from lx_checkwork ck \n" +
            "left join lx_signin sin on sin.lxCheckworkId=ck.id\n" +
            "left join lx_user u on u.id=ck.lxUserId\n" +
            "GROUP BY ck.id\n" +
            "order by ck.lxReleaseTime desc")
    public IPage<SigninTotal> queryAllSignStatistics(Page page);
    @Select("select ck.id,ck.lxCheckWorkName,u.lxUsername,ck.lxClazzName,\n" +
            "count(1) as totalNum,\n" +
            "sum(if(sin.lxSignInStatus=1,1,0)) as signinNum,\n" +
            "sum(if(sin.lxSignInStatus=2,1,0)) as lateNum ,\n" +
            "sum(if(sin.lxSignInStatus=3,1,0)) as leaveNum,\n" +
            "sum(if(sin.lxSignInStatus=4,1,0)) as absenceNum\n" +
            "from lx_checkwork ck \n" +
            "left join lx_signin sin on sin.lxCheckworkId=ck.id\n" +
            "left join lx_user u on u.id=ck.lxUserId\n" +
            "where ck.lxUserId=#{lxUserId}\n" +
            "GROUP BY ck.id\n" +
            "order by ck.lxReleaseTime desc")
    public IPage<SigninTotal> querySignStatisticsByUserIdPage(Page page,String lxUserId);
    @Select("select ck.id,ck.lxCheckWorkName,u.lxUsername,ck.lxClazzName,\n" +
            "count(1) as totalNum,\n" +
            "sum(if(sin.lxSignInStatus=1,1,0)) as signinNum,\n" +
            "sum(if(sin.lxSignInStatus=2,1,0)) as lateNum ,\n" +
            "sum(if(sin.lxSignInStatus=3,1,0)) as leaveNum,\n" +
            "sum(if(sin.lxSignInStatus=4,1,0)) as absenceNum,\n" +
            "ck.lxReleaseTime\n" +
            "from lx_checkwork ck \n" +
            "left join lx_signin sin on sin.lxCheckworkId=ck.id\n" +
            "left join lx_user u on u.id=ck.lxUserId\n" +
            "where ck.lxCheckWorkName LIKE concat('%',#{keyword},'%')\n" +
            "GROUP BY ck.id\n" +
            "order by ck.lxReleaseTime desc\n")
    public IPage<SigninTotal> querySignStatisticsByKeywordPage(Page page,String keyword);

    @Select("select ck.id,ck.lxCheckWorkName,ck.lxClazzName,\n" +
            "sum(if(sin.lxSignInStatus=1,1,0)) as signinNum,\n" +
            "sum(if(sin.lxSignInStatus=2,1,0)) as lateNum ,\n" +
            "sum(if(sin.lxSignInStatus=3,1,0)) as leaveNum,\n" +
            "sum(if(sin.lxSignInStatus=4,1,0)) as absenceNum\n" +
            "from lx_signin sin\n" +
            "left join lx_checkwork ck on sin.lxCheckworkId=ck.id\n" +
            "where ck.id=#{lxCheckworkId}")
    public IPage<SigninTotal> querySignStatisticsByCheck(Page page,String lxCheckworkId);
}
